# 一对多，多对多表关联
import datetime
import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select
from sqlalchemy.orm import declarative_base, mapped_column, Mapped, sessionmaker, relationship, aliased, outerjoin
from typing_extensions import Annotated
from typing import List

engine = create_engine('mysql://root:ZHabc123.@page.chenzao.fun:13306/testdb', echo=True)

Base = declarative_base()

int_pk = Annotated[int, mapped_column(Integer, primary_key=True)]
name_str_require_union = Annotated[str, mapped_column(String(128), nullable=False, unique=True)]
timestamp_not_null = Annotated[datetime.datetime, mapped_column(nullable=False)]


class Department(Base):
    __tablename__ = 'department'
    id: Mapped[int_pk]
    name: Mapped[name_str_require_union]
    
    # 使用 back_populates 参数，可以解决双向关联的麻烦, 可以在 Department 类中，通过 members 这个属性，来访问 Member 类
    # 属于显性的双向关联，这个比较常用，因为可以很清楚的知道，在 Department 类中，有一个 members 属性，是一个集合类型的
    # 但是在实际开发中，这种一对多的关联关系里，通常不会去写这种方便的关联关系，因为这样会降低数据库的性能。
    members: Mapped[List['Member']] = relationship(back_populates='department')
    
    
    def __repr__(self):
        # 直接打印对象的时候会调用这个方法，返回一个字符串，表示部门的ID和名称
        return f'Department(id={self.id}, name={self.name})'


class Member(Base):
    __tablename__ = 'member'
    id: Mapped[int_pk]
    department_id: Mapped[int] = mapped_column(ForeignKey("department.id"))
    name: Mapped[name_str_require_union]
    birthday: Mapped[timestamp_not_null]
    
    # 这不是跟数据库中的字段进行关联，而是跟 Department 类进行关联，放在内存中，方便后面使用。这是一个关系字段
    # backref 参数表示，在 Department 类中，有一个成员的字段，是 Member 类的实例，是一个集合类型的，相当于双向绑定
    # backref 这种方式的双向关联，虽然好用，但是单看 Department 类，无法知道有 members 这个属性，所以不太经常用
    # department: Mapped[Department] = relationship(lazy=False, backref="members")
    # 使用 back_populates 参数，可以解决双向关联的麻烦, 可以在 Department 类中，通过 members 这个属性，来访问 Member 类
    # 属于显性的双向关联，这个比较常用，因为可以很清楚的知道，在 Department 类中，有一个 members 属性，是一个集合类型的
    # 一对一是可以的
    department: Mapped[Department] = relationship(lazy=False, back_populates='members')
    
    def __repr__(self):
        return f'Member(id={self.id}, department_id={self.department_id}, name={self.name})'
    
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)


def insert_records_old(session):
    d1 = Department(name='财务部')
    # 此处 sqlalchemy 会有性能优化，不会立刻执行数据库的 insert 操作，有可能会等多个操作进行批处理    
    session.add(d1)
    
    # 如果执行了 flush 操作，那么 d1.id 就已经有了值，强制执行 insert 操作
    # session.flush()
    # 此时 d1.id 还没有被赋值, 还没有真正执行数据库的insert操作，所以这里会报错
    e1 = Member(department_id=d1.id, name='张三', birthday=datetime.datetime.now())
    session.add(e1)
    session.commit()

def insert_records(session):
    # 此方法使用了 relationship 字段，不需要手动指定外键的值，会自动给 department 和 member 都插入数据
    d1 = Department(name='人事部')
    e1 = Member(department=d1, name='zs', birthday="2000-10-10")
    session.add(e1)
    session.commit()
    
def select_records2(session):
    # 查询出所有的部门，并打印出来
    result = session.query(Member).filter(Member.id == 1).scalar()
    # 如果不查询 department，那么直接打印 Member 对象。
    print(result)
    # lazy 加载，在用到的时候，会再次查询部门数据库返回对应的数据
    # 这样懒加载有利有弊，好处是可以减少数据库的查询次数，但是如果用到需要多次查询，如果数据量很大，那么性能会降低
    print(result.department)
    # 如果希望一次性都给查出来，提高一下性能，可以使用在 relationship 时，增加 lazy=False，
    # 这样的话，会加入 join 操作，来联合查询。

def select_records(session):
    # 查询部门，并且把部门的员工都给打印出来
    dept = session.query(Department).filter(Department.id == 1).scalar()
    print(dept)
    print(dept.members)

def select_multiple(session):
    # 查询多个类
    # 查询出所有的人员，包括其部门
    # 把两张表通过Member的department关联起来，然后查询出所有的成员
    query = select(Member, Department).join(Member.department)
    result = session.execute(query)
    for row in result:
        print(row)

def select_with_alias(session):
    # 使用别名解决命名冲突的问题
    mem_cls = aliased(Member, name=f"mem")
    dep_cls = aliased(Department, name=f"dep")
    query = select(mem_cls, dep_cls).join(mem_cls.department.of_type(dep_cls), isouter=True)
    result = session.execute(query)
    for row in result:
        print(row)


def select_fields(session):
    # 查询出指定字段，而不是全部字段
    # select 指的是我要出巡出来什么东西，from 指的是从哪里来
    # 此处的 label 指的是给字段起一个别名，方便后面使用，对于查询结果没有什么影响
    query = select(Member.name.label('member_name'), Department.name.label('dep_name')).join_from(Member, Department)
    result = session.execute(query)
    for row in result:
        print(row)
        
def select_fields_outer(session):
    # 查询出指定字段，而不是全部字段
    # select 指的是我要出巡出来什么东西，from 指的是从哪里来
    # 此处的 label 指的是给字段起一个别名，方便后面使用，对于查询结果没有什么影响
    query = select(Member.name.label('member_name'), Department.name.label('dep_name'))\
        .select_from(outerjoin(Member, Department))
    result = session.execute(query)
    for row in result:
        print(row)
        

# INNER JOIN 与 OUTER JOIN 的区别
# 1. INNER JOIN 是内连接，OUTER JOIN 是外连接
# 2. INNER JOIN 查询的结果是两个表都有的数据，OUTER JOIN 查询的结果是两个表都有，但是有部分数据在两个表中都不存在
# 3. 默认情况下，查询的结果都是 INNER JOIN 的结果，如果需要使用 OUTER JOIN，那么需要指定
# LEFT OUTER JOIN 等于 LEFT JOIN 等于 OUTER JOIN 左外连接，查询的结果是左表的所有数据，右表的数据有则显示，没有则不显示
# LEFT INNER JOIN 与 INNER JOIN 的区别
# 1. LEFT INNER JOIN 是左连接，INNER JOIN 是内连接
# 2. LEFT INNER JOIN 查询的结果是左表的所有数据，右表的数据有则显示，没有则不显示
# 3. 默认情况下，查询的结果都是 INNER JOIN 的结果，如果需要使用 LEFT INNER JOIN，那么需要指定
# 4. 左连接的查询结果，是左表的所有数据，右表的数据有则显示，没有则不显示


def where_obj(session):
    dpt = session.get(Department, 1)
    query = select(Member).where(Member.department == dpt)
    # query = select(Member).where(Member.dep_id == dpt.id)
    result = session.execute(query)
    print(result)


# 开始操作数据库
session = Session()
# insert_records(session)
# select_records(session)
# select_with_alias(session)
# select_fields(session)
select_fields_outer(session)

